

-- ================================================================      
-- Author:   <Ei Ei Mon>      
-- Role:   <Programmer>      
-- Create date:  <2.10.2014>      
-- Description:  <sp_cr_CashReceivedByIndividualtoPayAgentRpt>      
-- Procedure Name: <[dbo].[sp_cr_CashReceivedByIndividualtoPayAgentRpt]>      
-- Return Type:  <Table>      
-- ================================================================      
-- UPDATED HISTORY      
-- ================================================================      
-- Name: May Myat Thu
-- Date: 22.12.2014   
-- Description: Add Issue Branch ID   
      
-- Name:        
-- Date:      
-- Description:       
-- Name:     
-- Date:    
-- Description:       
-- ================================================================ 

ALTER PROCEDURE [dbo].[sp_cr_CashReceivedByIndividualtoPayAgentRpt]
	
   @fromdate as varchar(30),
   @todate as varchar(30),
   @fromissuebranchid as varchar(100),
   @toissuebranchid as varchar(100),
   @fromreceiptid as varchar(100),
   @toreceiptid as varchar(100),
   @fromtoagentid as varchar(100),
   @totoagentid as varchar(100),
   @fromcurrencyid as nvarchar(4000),
   @tocurrencyid as nvarchar(4000),
   @frombankid as nvarchar(100),
   @tobankid as nvarchar(100),
   @fromuserid as varchar(100),
   @touserid as varchar(100),
   @orderby AS VARCHAR(100),
   @branchid as varchar(200)
 
as
BEGIN

declare @temp nvarchar(4000)
declare @colissuebranchid varchar(8000)
declare @colreceiptid varchar(8000)
declare @coltoagentid varchar(8000)
declare @colcurrencyid nvarchar(4000)
declare @colbankid nvarchar(4000)
declare @coluserid varchar(8000)

--if LEN (@fromdate) = 0 
	--set @fromdate = '2014-10-01 00:01:00.000'
--else
	--set @fromdate = substring(@fromdate,1,10)+' '+'00:00:00.000'

--if LEN (@todate) = 0 
	--set @todate = '2014-10-11 23:59:00.000' 
--else
	--set @todate = substring(@todate,1,10 )+' '+'23:59:59.200'

--IF(@fromdate > @todate )
--begin
	--set @temp = @fromdate
	--set @fromdate = @todate
	--set @todate = @temp
--end

--Issue Branch ID
IF(@fromissuebranchid <> '' AND @toissuebranchid <> '' AND @fromissuebranchid > @toissuebranchid)
BEGIN
 SET @temp = @fromissuebranchid
 SET @fromissuebranchid = @toissuebranchid
 SET @toissuebranchid = @temp
END

IF (@fromissuebranchid ='' and @toissuebranchid ='')
   SET @colissuebranchid =''
ELSE 
  BEGIN
      IF @fromissuebranchid ='' SET @fromissuebranchid = (SELECT TOP 1 BranchCode FROM dbo.Branch ORDER BY BranchId ASC)
      IF @toissuebranchid ='' SET @toissuebranchid = ( SELECT TOP 1 BranchCode FROM dbo.Branch ORDER BY BranchId DESC)
       SET @colissuebranchid = ' AND(BranchCode BETWEEN '''+@fromissuebranchid+ ''' AND '''+@toissuebranchid+''')'
END

--Receipt ID
IF(@fromreceiptid <> '' AND @toreceiptid <> '' AND @fromreceiptid > @toreceiptid)
BEGIN
 SET @temp= @fromreceiptid
 SET @fromreceiptid= @toreceiptid
 SET @toreceiptid= @temp
END

IF (@fromreceiptid ='' and @toreceiptid ='')
   SET @colreceiptid =''
ELSE 
  BEGIN
      IF @fromreceiptid='' SET @fromreceiptid= (SELECT TOP 1 ReceiptNo FROM dbo.CashRemittanceByAgent ORDER BY ReceiptNo ASC)
      IF @toreceiptid='' SET @toreceiptid= ( SELECT TOP 1 ReceiptNo FROM dbo.CashRemittanceByAgent ORDER BY ReceiptNo DESC)
       SET @colreceiptid = ' AND(ReceiptNo BETWEEN '''+@fromreceiptid+ ''' AND '''+@toreceiptid+''')'
END

-- To Agent ID
IF(@fromtoagentid <> '' AND @totoagentid <> '' AND @fromtoagentid > @totoagentid)
BEGIN
 SET @temp= @fromtoagentid
 SET @fromtoagentid= @totoagentid
 SET @totoagentid= @temp
END

IF (@fromtoagentid ='' and @totoagentid ='')
   SET @coltoagentid =''
ELSE 
  BEGIN
      IF @fromtoagentid='' SET @fromtoagentid= (SELECT TOP 1 agent_code FROM dbo.Agent ORDER BY agent_code ASC)
      IF @totoagentid='' SET @totoagentid= ( SELECT TOP 1 agent_code FROM dbo.Agent ORDER BY agent_code DESC)
       SET @coltoagentid = ' AND(agent_code BETWEEN '''+@fromtoagentid+ ''' AND '''+@totoagentid+''')'
END

--Currency ID
IF(@fromcurrencyid <> '' AND @tocurrencyid <> '' AND @fromcurrencyid > @tocurrencyid)
BEGIN
 SET @temp= @fromcurrencyid
 SET @fromcurrencyid= @tocurrencyid
 SET @tocurrencyid= @temp
END

IF (@fromcurrencyid ='' and @tocurrencyid ='')
   SET @colcurrencyid =''
ELSE 
  BEGIN
      IF @fromcurrencyid='' SET @fromcurrencyid= (SELECT TOP 1 CurrencyCode FROM dbo.Currency ORDER BY CurrencyCode ASC)
      IF @tocurrencyid='' SET @tocurrencyid= ( SELECT TOP 1 CurrencyCode FROM dbo.Currency ORDER BY CurrencyCode DESC)
       SET @colcurrencyid = ' AND(CurrencyCode BETWEEN  N'''+@fromcurrencyid+ ''' AND  N'''+@tocurrencyid+''')'
END

--Bank Cheque ID
IF(@frombankid <> '' AND @tobankid <> '' AND @frombankid > @tobankid)
BEGIN
 SET @temp= @frombankid
 SET @frombankid= @tobankid
 SET @tobankid= @temp
END

IF (@frombankid ='' and @tobankid ='')
   SET @colbankid =''
ELSE 
  BEGIN
      IF @frombankid='' SET @frombankid= (SELECT TOP 1 BankCode FROM dbo.Bank ORDER BY BankCode ASC)
      IF @tobankid='' SET @tobankid= ( SELECT TOP 1 BankCode FROM dbo.Bank ORDER BY BankCode DESC)
       SET @colbankid = ' AND(BankCode BETWEEN  N'''+@frombankid+ ''' AND  N'''+@tobankid+''')'
END

--User ID
IF(@fromuserid <> '' AND @touserid <> '' AND @fromuserid > @touserid)
BEGIN
 SET @temp= @fromuserid
 SET @fromuserid= @touserid
 SET @touserid= @temp
END

IF (@fromuserid ='' and @touserid ='')
   SET @coluserid =''
ELSE 
  BEGIN
      IF @fromuserid='' SET @fromuserid= (SELECT TOP 1 UserName FROM dbo.[User] ORDER BY UserName ASC)
      IF @touserid='' SET @touserid= ( SELECT TOP 1 UserName FROM dbo.[User] ORDER BY UserName DESC)
       SET @coluserid = ' AND(UserName BETWEEN '''+@fromuserid+ ''' AND '''+@touserid+''')'
END


BEGIN
	DECLARE @groupname AS VARCHAR(200)
	DECLARE @title AS VARCHAR(200)
	IF(@orderby='DateTime')
		BEGIN
			SET @groupname ='a.Date'
			SET @title = '''Date'''
		END
	ELSE IF(@orderby='IssueBranchID')
		BEGIN
			SET @groupname ='BranchCode'
			SET @title = '''Issue Branch ID'''
		END
	ELSE IF(@orderby='ReceiptID')
		BEGIN
			SET @groupname ='ReceiptNo'
			SET @title = '''Receipt'''
		END
	ELSE IF(@orderby='ToAgentID')
		BEGIN
			SET @groupname ='agent_code'	
			SET @title = '''ToAgent'''
		END
	ELSE IF(@orderby='CurrencyID')
		BEGIN
			SET @groupname ='CurrencyCode'
			SET @title = '''Currency'''
		END
	ELSE IF(@orderby='BankID')
		BEGIN
			SET @groupname ='BankCode'	
			SET @title = '''Bank'''
		END
	ELSE IF(@orderby='UserID')
		BEGIN
			SET @groupname ='UserName'
			SET @title = '''User'''
		END
END	

	declare @sql nvarchar(4000)

	set @sql='SELECT DISTINCT '+@groupname+' As GroupName,'
	+@title+' As Title,
	ROW_NUMBER() over(order by CurrencyCode,'+@groupname+')RNo,
	a.Date,a.Time,ReceiptNo,agent_code,name,Code,
	Sender,CurrencyCode,a.TotalPaymentAmount,
	a.ExtraFees,Receiver,NRC,Address,UserName,BankCode,BranchCode
	from
	(select distinct
	convert(char(10),crba.ReceivedDate,103)Date, 
	(substring(cast(crba.ReceivedDate as nvarchar(30)),13,5)+'' ''+substring(cast(crba.ReceivedDate as nvarchar(30)),18,2))Time,
	crba.ReceiptNo,
	a.agent_code,a.name,
	ac.Code,crbad.Sender,c.CurrencyCode,
	crba.TotalPaymentAmount,crba.ExtraFees,
	crbad.Receiver,crbad.NRC,crbad.Address,
	u.UserName,isnull(b.BankCode,'''')BankCode,branch.BranchCode
	from CashRemittanceByAgent crba
	inner join CashRemittanceByAgentDetails crbad on crba.ReceiptID=crbad.ReceiptID
	left join Branch branch on branch.BranchId = crbad.ToBranchID
	inner join AddCode ac on ac.AddCodeId=crbad.AddCodeID
	left join Agent a on a.agent_id=crbad.ToAgentID
	inner join Currency c on c.CurrencyId = crba.PaymentCurrencyID
	left join Bank b on b.BankId = crba.BankID
	inner join [User] u on u.UserId = crba.CreatedBy
	where crba.FromAgentID is null 
	and (crba.ReceivedDate between ''' + @fromdate + ''' and ''' + @todate+''')
	and crba.DefaultBranchID = '+@branchid+'
	union
	select distinct
	convert(char(10),crba.ReceivedDate,103)Date, 
	(substring(cast(crba.ReceivedDate as nvarchar(30)),13,5)+''''+substring(cast(crba.ReceivedDate as nvarchar(30)),18,2))Time,
	crba.ReceiptNo,
	a.agent_code,a.name,
	ac.Code,crbad.Sender,c.CurrencyCode,
	crba.TotalPaymentAmount,crba.ExtraFees,
	crbad.Receiver,crbad.NRC,crbad.Address,
	u.UserName,isnull(b.BankCode,'''')BankCode,branch.BranchCode
	from CashRemittanceByAgent_Archive crba
	inner join CashRemittanceByAgentDetails_Archive crbad on crba.ReceiptID=crbad.ReceiptID
	left join Branch branch on branch.BranchId = crbad.ToBranchID
	left join AddCode ac on ac.AddCodeId=crbad.AddCodeID
	left join Agent a on a.agent_id=crbad.ToAgentID
	inner join Currency c on c.CurrencyId = crba.PaymentCurrencyID
	left join Bank b on b.BankId = crba.BankID
	inner join [User] u on u.UserId = crba.CreatedBy
	where crba.FromAgentID is null
	and (crba.ReceivedDate between ''' + @fromdate + ''' and ''' + @todate+''')
	and crba.DefaultBranchID = '+@branchid+'
	)a where 1=1'
	+@colissuebranchid+@colreceiptid+@coltoagentid+@colcurrencyid+@colbankid+@coluserid 
	exec (@sql)
	--select (@sql)
 
END

GO


